﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Entities;

namespace DataAccess
{
    public class TrainDAL : BaseObject
    {
        public TrainDAL() :base()
        {

        }

        #region Public Methods
        ///<summary>
        ///Create Obj from ireader.
        ///</summary>
       
        private Train GetObjectFromReader(IDataReader reader) 
         {
             Train temp= new Train();
             temp.TrainID = (reader["TrainID"] is DBNull) ? string.Empty : (System.String)reader["TrainID"];
             temp.RouteID = (reader["RouteID"] is DBNull) ? int.MinValue : (System.Int32)reader["RouteID"];
             temp.MaxSpeed = (reader["MaxSpeed"] is DBNull) ? float.MinValue : (float)reader["MaxSpeed"];
             temp.MinSpeed = (reader["MinSpeed"] is DBNull) ? float.MinValue : (float)reader["MinSpeed"];
             temp.StartTime = (reader["StartTime"] is DBNull) ? string.Empty : (System.String)reader["StartTime"];
             temp.Status = (reader["Status"] is DBNull) ? false : (System.Boolean)reader["Status"]; 
             return temp;
         }
       
        ///<summary>
        ///Get List Train and store in a list 
        ///</summary>
        public List<Train> GetList_Train(string whereSql, string orderBy)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(usp_SelecttbTrainsDynamic, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@WhereCondition", whereSql));
                cmd.Parameters.Add(new SqlParameter("@OrderByExpression", orderBy));
                conn.Open();
                List<Train> lst = new List<Train>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) )
                {
                    while (reader.Read())
                     {
                         lst.Add( GetObjectFromReader(reader) );
                     }
                }
                conn.Close();
                return lst;
		    }
		 }

        ///<summary>
        ///Get Train By ID
        ///</summary>
        public Train GetList_Train_By_ID(string TrainID)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(usp_SelecttbTrainsByID, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@TrainID ", TrainID));
                Train temp = new Train();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        temp = GetObjectFromReader(reader);
                    }
                }
                conn.Close();
                return temp;
		    }
		 }

        ///<summary>
        ///Using for paging data 
        ///</summary>
        /// <param name="pageIndex">Number of pages</param>
        /// <param name="PageSize">Number of a rows per page</param>
        /// <param name="conditions">Where  fields</param>
        /// <param name="groupBy">Group by fields</param>
        public DataTable Get_TrainAsPaging(string sWhere, string sOrderBy, int pageIndex, int pageSize)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(usp_SelecttbTrainsPaged, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Where", sWhere));
                cmd.Parameters.Add(new SqlParameter("@OrderBy", sOrderBy));
                cmd.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
                cmd.Parameters.Add(new SqlParameter("@PageSize", pageSize));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
		       {
                 conn.Open();
                 DataTable tbl = new DataTable();
                 dataAdapter.Fill(tbl);
                 conn.Close();
                 return tbl;
		       }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Insert new User
        ///</summary>
        public bool Insert_Train(Train temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(usp_InserttbTrain, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@TrainID", temp.TrainID));
                cmd.Parameters.Add(new SqlParameter("@RouteID", temp.RouteID));
                cmd.Parameters.Add(new SqlParameter("@MaxSpeed", temp.MaxSpeed));
                cmd.Parameters.Add(new SqlParameter("MinSpeed", temp.MinSpeed));
                cmd.Parameters.Add(new SqlParameter("@StartTime", temp.StartTime));
                cmd.Parameters.Add(new SqlParameter("@Statusd", temp.Status));

                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();

                 return true;
		        }
                catch(SqlException ex)
		        {
                    return false;
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }  
		 }

        ///<summary>
        ///Update a User
        ///</summary>
        public void Update_Train(Train temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(usp_UpdatetbTrain, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@TrainID", temp.TrainID));
                cmd.Parameters.Add(new SqlParameter("@RouteID", temp.RouteID));
                cmd.Parameters.Add(new SqlParameter("@MaxSpeed", temp.MaxSpeed));
                cmd.Parameters.Add(new SqlParameter("MinSpeed", temp.MinSpeed));
                cmd.Parameters.Add(new SqlParameter("@StartTime", temp.StartTime));
                cmd.Parameters.Add(new SqlParameter("@Statusd", temp.Status));

                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        public void Delete_Train(Train temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_DeletetbTrain, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@User_Name", temp.TrainID));
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        #endregion
    }
 }

